/**
 * Copyright 2011 Google Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.google.api.adwords.starterkit.db.tables;

import com.google.api.adwords.starterkit.db.keys.CampaignKey;
import com.google.api.adwords.starterkit.db.structures.AdGroupStructure;
import com.google.api.adwords.starterkit.db.structures.CampaignStructure;
import com.google.api.adwords.starterkit.utils.DumpExceptions;
import com.google.api.adwords.v201101.cm.AdGroupBids;
import com.google.api.adwords.v201101.cm.Bid;
import com.google.api.adwords.v201101.cm.BudgetBudgetDeliveryMethod;
import com.google.api.adwords.v201101.cm.BudgetBudgetPeriod;
import com.google.api.adwords.v201101.cm.BudgetOptimizerAdGroupBids;
import com.google.api.adwords.v201101.cm.CampaignStatus;
import com.google.api.adwords.v201101.cm.ConversionOptimizerAdGroupBids;
import com.google.api.adwords.v201101.cm.ManualCPCAdGroupBids;
import com.google.api.adwords.v201101.cm.ManualCPMAdGroupBids;
import com.google.api.adwords.v201101.cm.Money;
import com.google.api.adwords.v201101.cm.UserStatus;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;

/**
 * The Class CampaignUtils encapsulates most of the access to Campaigns DB.
 * 
 * @author rodrigo.f.vale@gmail.com  (Rodrigo Vale)
 */
public class CampaignTable {
  private PreparedStatement prepNew;
  private PreparedStatement prepUpdate;
  private PreparedStatement prepLoad;
  private PreparedStatement prepTouched;
  private PreparedStatement prepUpdated;
  PreparedStatement prepStatsUpdate;
  private PreparedStatement prepExists;
  private PreparedStatement prepLocalId;
  private PreparedStatement prepSetId;
  private Connection conn;
  private PreparedStatement prepResetIsBanned;
  private PreparedStatement prepResetIsUpdated;
  
  /**
   * Instantiates a new campaign utils.
   *
   * @param conn the DB connection
   */
  public CampaignTable(Connection conn) {
    this.conn = conn;
    try {
      Statement stat = conn.createStatement();
      stat.executeUpdate(
            "CREATE TABLE IF NOT EXISTS Campaigns "
          + "  ( "
          + "     LocalId            INTEGER PRIMARY KEY, "
          + "     CampaignId         LONG, "
          + "     Name               STRING, "
          + "     BudgetAmount       INTEGER, "
          + "     BudgetPeriod       STRING, "
          + "     StartDate          STRING, "
          + "     EndDate            STRING, "
          + "     Status             STRING, "
          + "     IsStatusModifiable    BOOLEAN DEFAULT 1, "
          + "     IsBudgetModifiable    BOOLEAN DEFAULT 1, "
          + "     IsEndDateModifiable   BOOLEAN DEFAULT 1, "
          + "     IsUpdated            BOOLEAN DEFAULT 0, "
          + "     Ctr                FLOAT, "
          + "     Clicks             INTEGER, "
          + "     Cost               FLOAT, "
          + "     Impressions        INTEGER, "
          + "     Conversions        INTEGER, "
          + "     Transactions       INTEGER, "
          + "     IsLanguageModified   BOOLEAN DEFAULT 0, "
          + "     IsGeoModified        BOOLEAN DEFAULT 0, "
          + "     IsNetworkModified    BOOLEAN DEFAULT 0, "
          + "     IsBiddingModified    BOOLEAN DEFAULT 0, "
          + "     IsBidModified        BOOLEAN DEFAULT 0, "
          + "     IsTouched            BOOLEAN DEFAULT 0, "
          + "     IsStatsAvailable     BOOLEAN DEFAULT 0, "
          + "     IsBanned             BOOLEAN DEFAULT 0, "
          + "     BiddingStrategy    STRING DEFAULT '', "
          + "     PricingModel       STRING, "
          + "     BidCeiling         INTEGER, "
          + "     OldBiddingStrategy STRING, "
          + "     LastUpdate         INTEGER, "
          + "     AveragePosition    FLOAT, "
          + "     AdGroupMaxCpa      FLOAT, "
          + "     CostPerConverstion FLOAT, "
          + "     CostPerTransaction FLOAT, "
          + "     FirstPageCpc       FLOAT, "
          + "     IsPlatformModified BOOLEAN DEFAULT 0, "
          + "     DeliveryMethod     STRING"
          + "  ); ");
      
      stat.executeUpdate(
            "CREATE INDEX IF NOT EXISTS CampaignsNameIndex "
          + "  ON Campaigns (Name);");
      stat.executeUpdate(
            "CREATE INDEX IF NOT EXISTS CampaignsCampaignIdIndex "
          + "  ON Campaigns (CampaignId);");      
            
      prepLocalId = conn.prepareStatement(
          "SELECT LocalId FROM Campaigns WHERE CampaignId = ?;");
      prepExists = conn.prepareStatement(
          "SELECT Name FROM Campaigns WHERE Name = ?;");
      prepLoad = conn.prepareStatement(
          "SELECT * FROM Campaigns WHERE Name = ?;");

      prepTouched = conn.prepareStatement(
          "UPDATE Campaigns SET IsTouched = ? WHERE Name = ?;");
      prepUpdated = conn.prepareStatement(
          "UPDATE Campaigns SET IsUpdated = ? WHERE Name = ?;");
      prepSetId = conn.prepareStatement(
          "UPDATE Campaigns SET CampaignId = ? WHERE Name = ?;");
      
      prepNew = conn.prepareStatement(
          "REPLACE INTO Campaigns (CampaignId, Name, BudgetAmount, " +
          "BudgetPeriod, StartDate, EndDate, Status, IsStatusModifiable, " +
          "IsBudgetModifiable, IsEndDateModifiable, IsUpdated, Ctr, Clicks, " +
          "Cost, Impressions, Conversions, Transactions, IsLanguageModified, " +
          "IsNetworkModified, IsGeoModified, IsStatsAvailable, IsBanned, " +
          "IsTouched, BiddingStrategy, PricingModel, BidCeiling, " +
          "IsBiddingModified, OldBiddingStrategy, IsBidModified, " +
          "IsPlatformModified, DeliveryMethod) VALUES (?, ?, ?, " +
          "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, " +
          "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");
      
      prepUpdate = conn.prepareStatement(
          "REPLACE INTO Campaigns (LocalId, CampaignId, Name, BudgetAmount, " +
          "BudgetPeriod, StartDate, EndDate, Status, IsStatusModifiable, " +
          "IsBudgetModifiable, IsEndDateModifiable, IsUpdated, Ctr, Clicks, " +
          "Cost, Impressions, Conversions, Transactions, IsLanguageModified, " +
          "IsNetworkModified, IsGeoModified, IsStatsAvailable, IsBanned, " +
          "IsTouched, BiddingStrategy, PricingModel, BidCeiling, " +
          "IsBiddingModified, OldBiddingStrategy, " +
          "IsBidModified, IsPlatformModified, DeliveryMethod) VALUES (?, ?, ?, " +
          "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, " +
          "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"); 
      
      prepStatsUpdate = conn.prepareStatement(
          "UPDATE Campaigns SET Ctr = ?, Clicks = ?, Cost = ?, "
        + "Impressions = ?, Conversions = ?, Transactions = ?, "
        + "averagePosition = ?, adGroupMaxCpa = ?, "
        + "costPerConverstion = ?, "
        + "costPerTransaction = ?, "
        + "firstPageCpc = ?, IsStatsAvailable = 1 "
        + "WHERE campaignId = ?");
      
      prepResetIsBanned = conn.prepareStatement(
          "UPDATE Campaigns SET IsBanned = ?");
      prepResetIsUpdated = conn.prepareStatement(
          "UPDATE Campaigns SET IsUpdated = ?");
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }    
  }

  /**
   * Gets the CampaignStructure from a generic SQL request.
   *
   * @param rs the ResultSet of a generic SQL request
   * @return all fields managed by Campaign wrapper 
   */
  public CampaignStructure getResultSet(ResultSet rs) {
    CampaignStructure campaignStructure = new CampaignStructure();
    try {
      campaignStructure.setName(rs.getString("Name"));
      campaignStructure.setStartDate(rs.getString("StartDate"));
      campaignStructure.setEndDate(rs.getString("EndDate"));
      campaignStructure.setBudgetAmount(rs.getLong("BudgetAmount"));
      
      String budgetPeriod = rs.getString("BudgetPeriod").toUpperCase();      
      campaignStructure.setBudgetPeriod(budgetPeriod); 
      
      String status = rs.getString("Status").toUpperCase();
      campaignStructure.setStatus(status);
      
      campaignStructure.setLocalId(rs.getInt("LocalId"));
      campaignStructure.setCampaignId(rs.getLong("CampaignId"));
      campaignStructure.setBudgetAmount(rs.getLong("BudgetAmount"));
      campaignStructure.setBudgetModifiable(rs.getBoolean("IsBudgetModifiable"));
      campaignStructure
          .setEndDateModifiable(rs.getBoolean("IsEndDateModifiable"));
      campaignStructure.setStatusModifiable(rs.getBoolean("IsStatusModifiable"));
      campaignStructure.setCtr(rs.getFloat("Ctr"));
      campaignStructure.setClicks(rs.getLong("Clicks"));
      campaignStructure.setCost(rs.getFloat("Cost"));
      campaignStructure.setImpressions(rs.getLong("Impressions"));
      campaignStructure.setConversions(rs.getLong("Conversions"));
      campaignStructure.setTransactions(rs.getLong("Transactions"));
      campaignStructure
          .setLanguageModified(rs.getBoolean("IsLanguageModified"));
      campaignStructure
          .setPlatformModified(rs.getBoolean("IsPlatformModified"));
      campaignStructure.setNetworkModified(rs.getBoolean("IsNetworkModified"));
      campaignStructure.setGeoModified(rs.getBoolean("IsGeoModified"));
      campaignStructure.setBiddingModified(rs.getBoolean("IsBiddingModified"));
      campaignStructure.setBidModified(rs.getBoolean("IsBidModified"));
      campaignStructure.setStatsAvailable(rs.getBoolean("IsStatsAvailable"));
      campaignStructure.setBanned(rs.getBoolean("IsBanned"));
      campaignStructure.setBiddingStrategy(rs.getString("BiddingStrategy"));
      campaignStructure
          .setOldBiddingStrategy(rs.getString("OldBiddingStrategy"));
      campaignStructure.setPricingModel(rs.getString("PricingModel"));
      campaignStructure.setBidCeiling(rs.getLong("BidCeiling"));
      BudgetBudgetDeliveryMethod budgetBudgetDeliveryMethod = null;
      String deliveryMethod = "";
      if (rs.getString("DeliveryMethod").length() > 0) {
        deliveryMethod = rs.getString("DeliveryMethod").toUpperCase();
      }
      campaignStructure.setDeliveryMethod(deliveryMethod);
      return campaignStructure;
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
    return null;
  }

  /**
   * Loads a campaign from DB.
   *
   * @param campaignKey the campaign primary key
   * @return the campaign structure
   */
  public CampaignStructure get(CampaignKey campaignKey) {
    ResultSet rs = null;
    CampaignStructure campaignStrucutre = null;
    try {
      prepLoad.setString(1, campaignKey.getName());      
      rs = prepLoad.executeQuery();
      if (rs.next()) {
        campaignStrucutre = getResultSet(rs);
      }
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }   
    return campaignStrucutre;
  }

  /**
   * Save a campaign to DB and returns its LocalId.
   *
   * @param campaignStructure the campaign structure
   * @param updated flag to point that the Campaign has an update to AdWords
   * @param touched flag to point that the Campaign has been touched by
   * StarterKit
   * @return the campaign structure
   */
  public CampaignStructure save(CampaignStructure campaignStructure, 
      boolean updated, boolean touched) {
    ResultSet rs = null;
    try {
      PreparedStatement prep;
      int count = 1;
      if (campaignStructure.getLocalId() == 0) {
        prep = prepNew;
      } else {
        prep = prepUpdate;
        prep.setLong(count++, campaignStructure.getLocalId());
      }        
      prep.setLong(count++, campaignStructure.getCampaignId());      
      prep.setString(count++, campaignStructure.getName());
      prep.setLong(count++, campaignStructure.getBudgetAmount());
      prep.setString(count++, campaignStructure.getBudgetPeriod().getValue());
      prep.setString(count++, campaignStructure.getStartDate());        
      prep.setString(count++, campaignStructure.getEndDate());
      prep.setString(count++, 
          campaignStructure.getStatus().getValue());
      prep.setBoolean(count++, campaignStructure.isStatusModifiable());
      prep.setBoolean(count++, campaignStructure.isBudgetModifiable());
      prep.setBoolean(count++, campaignStructure.isEndDateModifiable());
      prep.setBoolean(count++, updated);
      prep.setFloat(count++, campaignStructure.getCtr());
      prep.setLong(count++, campaignStructure.getClicks());
      prep.setFloat(count++, campaignStructure.getCost());
      prep.setLong(count++, campaignStructure.getImpressions());
      prep.setLong(count++, campaignStructure.getConversions());
      prep.setLong(count++, campaignStructure.getTransactions());
      prep.setBoolean(count++, campaignStructure.isLanguageModified());
      prep.setBoolean(count++, campaignStructure.isModifiedNetwork());
      prep.setBoolean(count++, campaignStructure.isModifiedGeo());
      prep.setBoolean(count++, campaignStructure.isStatsAvailable());
      prep.setBoolean(count++, campaignStructure.isBanned());
      prep.setBoolean(count++, touched);
      prep.setString(count++, campaignStructure.getBiddingStrategy());
      prep.setString(count++, campaignStructure.getPricingModel());
      prep.setLong(count++, campaignStructure.getBidCeiling());
      prep.setBoolean(count++, campaignStructure.isBiddingModified());
      prep.setString(count++, campaignStructure.getOldBiddingStrategy());
      prep.setBoolean(count++, campaignStructure.isBidModified());
      prep.setBoolean(count++, campaignStructure.isPlatformModified());
      if (campaignStructure.getDeliveryMethod() != null) {
        prep.setString(count++, 
            campaignStructure.getDeliveryMethod().getValue());
      } else {
        prep.setString(count++, "STANDARD");        
      }
      prep.executeUpdate();

      rs = prep.getGeneratedKeys();      
      campaignStructure.setLocalId(rs.getInt(1));      
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }     
    return campaignStructure;
  }

  /**
   * Informs to StarterKit that the Campaign was processed by the application.
   * This avoid to starterkit to pause the Campaign automatically when 
   * "AutoPause" in settings file is true
   *
   * @param campaignKey the primary key of campaign
   * @param touched boolean flag 
   */
  public void setIsTouched(CampaignKey campaignKey, boolean touched) {
    try {
      prepTouched.setBoolean(1, touched);
      prepTouched.setString(2, campaignKey.getName());
      prepTouched.execute();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  }

  /**
   * Informs to StarterKit that the Campaign was updated by the application.
   * During the AdWords synchronization process only updated Campaigns will 
   * be processed by StarterKit.
   *  
   * @param campaignKey the campaign primary key
   * @param updated boolean flag 
   */
  public void setIsUpdated(CampaignKey campaignKey, boolean updated) {
    try {
      prepUpdated.setBoolean(1, updated);
      prepUpdated.setString(2, campaignKey.getName());
      prepUpdated.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  }

  /**
   * Check to see if the Campaign exists.
   *
   * @param campaignKey the campaign key
   * @return true, if successful
   */
  public boolean exists(CampaignKey campaignKey) {
    ResultSet rs = null;
    boolean exists = false;
    try {
      prepExists.setString(1, campaignKey.getName());      
      rs = prepExists.executeQuery();
      if (rs.next()) {
        exists = true;
      }
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }  
    return exists;
  }

  /**
   * Gets the local id from DB based on CampaignId from AdWords.
   *
   * @param campaignId the campaign id
   * @return the local id
   */
  public int getLocalId(long campaignId) {
    int localId = 0;
    ResultSet rs = null;
    try {
      prepLocalId.setLong(1, campaignId);
      rs = prepLocalId.executeQuery();
      if (rs.next()) {
        localId = rs.getInt("LocalId");
      }
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }      
    return localId;
  }
  
  /**
   * Sets the campaignID from AdWords in a Campagin on DB.
   *
   * @param campaignKey the campaign primary key
   * @param campaignId the campaign id
   */
  public void setId(CampaignKey campaignKey, long campaignId) {
    try {
      prepSetId.setLong(1, campaignId);
      prepSetId.setString(2, campaignKey.getName());
      prepSetId.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }    
  }
  
  /**
   * Set geo flag that informs if a campaign geolocation was modified.
   *
   * @param campaignKey the campaign key
   * @param geoFlag the geo flag
   */
  public void setGeoFlag(CampaignKey campaignKey, boolean geoFlag) {
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
          "UPDATE Campaigns SET IsGeoModified = ? WHERE Name = ?;");
      prep.setBoolean(1, geoFlag);
      prep.setString(2, campaignKey.getName());
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }
  }

  /**
   * Sets the language flag associated with modification on the LanguageTarget
   *
   * @param campaignKey the campaign key
   * @param langFlag the lang flag
   */
  public void setLangFlag(CampaignKey campaignKey, boolean langFlag) {
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
          "UPDATE Campaigns SET IsLanguageModified = ? WHERE Name = ?;");
      prep.setBoolean(1, langFlag);
      prep.setString(2, campaignKey.getName());
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }    
  }
  
  /**
   * Sets the platform flag associated with modification on the PlatformTarget
   *
   * @param campaignKey the campaign key
   * @param platformFlag the platform flag
   */
  public void setPlatformFlag(CampaignKey campaignKey, boolean platformFlag) {
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
          "UPDATE Campaigns SET ModifiedPlatform = ? WHERE Name = ?;");
      prep.setBoolean(1, platformFlag);
      prep.setString(2, campaignKey.getName());
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }        
  }
  
  /**
   * Sets the network flag associated with modification on the NetworkTarget
   *
   * @param campaignKey the campaign key
   * @param netFlag the net flag
   */
  public void setNetFlag(CampaignKey campaignKey, boolean netFlag) {
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
          "UPDATE Campaigns SET IsNetworkModified = ? WHERE Name = ?;");
      prep.setBoolean(1, netFlag);
      prep.setString(2, campaignKey.getName());
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }        
  }  
  
  /**
   * Reset all campaigns status flags to its default values
   */
  public void resetStatus() {
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
            "UPDATE Campaigns SET IsUpdated = 0, IsTouched = 0;");
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }     
  }
  
  /**
   * Gets the campaign bidding strategy.
   *
   * @return the bidding strategy
   */
  public HashMap<Integer , AdGroupBids> getBiddingStrategy() {
    HashMap<Integer , AdGroupBids> biddingStrategies 
        = new HashMap<Integer , AdGroupBids>();
    PreparedStatement prep = null;
    ResultSet rs = null;
    try {
      prep = conn.prepareStatement(
          "SELECT LocalId, BiddingStrategy FROM Campaigns;");
      rs = prep.executeQuery();
      while (rs.next()) {
        int campaignId = rs.getInt("LocalId");
        String biddingStrategy = rs.getString("BiddingStrategy");
          
        AdGroupBids bids = null;
        if (biddingStrategy.equals("MANUALCPC")) {
          bids = new ManualCPCAdGroupBids();
        } else if (biddingStrategy.equals("MANUALCPM")) {
          bids = new ManualCPMAdGroupBids();
        } else if (biddingStrategy.equals("BUDGETOPTIMIZER")) {
          bids = new BudgetOptimizerAdGroupBids();
        } else if (biddingStrategy.equals("CONVERSIONOPTIMIZER")) {
          bids = new ConversionOptimizerAdGroupBids();
        }        
        biddingStrategies.put(campaignId, bids);
      }
      rs.close();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }         
    return biddingStrategies;
  }  
  
  /**
   * Reset all campaigns flags to its default values 
   */
  public void reset() {
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
            "UPDATE Campaigns SET IsUpdated = 0, IsGeoModified = 0, " 
          + "IsLanguageModified = 0, IsNetworkModified = 0, "
          + "IsBiddingModified = 0, IsTouched = 0, "
          + "IsStatusModifiable = 1, IsBudgetModifiable = 1, " 
          + "IsEndDateModifiable = 1, OldBiddingStrategy = '';");
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }          
  }

  /**
   * Gets the campaign local id values from DB and map it to the campaign name
   *
   * @return the map from campaign name to local id 
   */
  public HashMap<String, Integer> getAllCampaignsLocalId() {
    HashMap<String, Integer> campaingsLocalId = new HashMap<String, Integer>();
    
    PreparedStatement prep = null;
    ResultSet rs = null;
    try {
      prep = conn.prepareStatement(
          "SELECT LocalId, Name FROM Campaigns;");
      rs = prep.executeQuery();
      while (rs.next()) {
        int campaignId = rs.getInt("LocalId");
        String name = rs.getString("Name");
        campaingsLocalId.put(name, campaignId);
      }
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }      
    return campaingsLocalId;
  }
  
  /**
   * Gets the map between campaign local id and local id values from DB
   *
   * @return the map between campaign local id and local id 
   */
  public HashMap<Long, Integer> getCampaignId2LocalId() {
    HashMap<Long, Integer> campaingsLocalId = new HashMap<Long, Integer>();
    
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
          "SELECT CampaignId, LocalId FROM Campaigns;");
      ResultSet rs = prep.executeQuery();
      while (rs.next()) {
        int localId = rs.getInt("LocalId");
        long campaignId = rs.getLong("CampaignId");
        campaingsLocalId.put(campaignId, localId);
      }
      rs.close();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }      
    return campaingsLocalId;
  }
    
  /**
   * Reset all campaigns flags to its default values 
   */
  public void resetGeoTargetFlags() {
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
          "UPDATE Campaigns SET IsGeoModified = 0 " +
          "WHERE IsGeoModified = 1");
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }          
  }
  
  /**
   * Reset all campaigns flags to its default values 
   */
  public void resetLanguageTargetFlags() {
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
          "UPDATE Campaigns SET IsLanguageModified = 0 " +
          "WHERE IsLanguageModified = 1");
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }          
  }

  /**
   * Reset all campaigns flags to its default values 
   */
  public void resetNetworkTargetFlags() {
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
          "UPDATE Campaigns SET IsNetworkModified = 0 " +
          "WHERE IsNetworkModified = 1");
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }          
  }

  /**
   * Reset all campaigns flags to its default values 
   */
  public void resetPlatformTargetFlags() {
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
            "UPDATE Campaigns SET IsPlatformModified = 0 "
          + "WHERE IsPlatformModified = 1");
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }          
  }

  /**
   * Resets IsBanned flag in all rows of AdGroups table
   */
  public void resetIsBanned(boolean isBanned) {
    try {
      prepResetIsBanned.setBoolean(1, isBanned);
      prepResetIsBanned.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }     
  }

  /**
   * Resets IsUpdated flag in all rows of AdGroups table
   */
  public void resetIsUpdated(boolean isUpdated) {
    try {
      prepResetIsUpdated.setBoolean(1, isUpdated);
      prepResetIsUpdated.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }     
  }

  /**
   * Set the Status to PAUSED for all Campaigns not touched by the 
   * application.
   */
  public void autoPause() {
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
            "UPDATE Campaigns SET Status = ? WHERE isTouched = 0 " +
            "AND Status NOT IN (?, ?)");
      prep.setString(1, UserStatus.PAUSED.getValue());
      prep.setString(2, UserStatus.PAUSED.getValue());
      prep.setString(3, UserStatus.DELETED.getValue());
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }       
  }

  /**
   * Grant that all banned Campaigns will not be updated by StarterKit
   * synchronization process.
   */
  public void grantBanned() {
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
            "UPDATE Campaigns SET IsUpdated = 0, IsTouched = 0 " +
            "WHERE IsBanned = 1");
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }      
  }
  
  public ResultSet getModified() {
    ResultSet rs = null;
    try {
      PreparedStatement prep = conn
        .prepareStatement("SELECT * FROM Campaigns WHERE IsUpdated = 1;");
      rs = prep.executeQuery();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
    return rs;
  }
  
  public void resetStats() {
    Statement stat = null;
    try {
      stat = conn.createStatement();
      stat.executeUpdate(
          "UPDATE Campaigns SET Ctr = 0, Clicks = 0, Cost = 0, "
        + "Impressions = 0, Conversions = 0, Transactions = 0, "
        + "IsStatsAvailable =  0;");
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (stat != null) {
        try {
          stat.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }  
  }
  
  public void saveStatsBatch(float ctr, long clicks, float cost, 
      long impressions, long conversions, long transactions, 
      float averagePosition, float adGroupMaxCpa, float firstPageCpc,
      long campaignId) {
    try {
      prepStatsUpdate.setFloat(1, ctr);
      prepStatsUpdate.setLong(2, clicks);
      prepStatsUpdate.setFloat(3, cost);
      prepStatsUpdate.setLong(4, impressions);
      prepStatsUpdate.setLong(5, conversions);
      prepStatsUpdate.setLong(6, transactions);
      prepStatsUpdate.setDouble(7, averagePosition);
      prepStatsUpdate.setDouble(8, adGroupMaxCpa);
      
      if (conversions > 0) {
        prepStatsUpdate
            .setDouble(9, cost / conversions);
      } else {
        prepStatsUpdate.setDouble(9, 0);
      }
      if (transactions > 0) {
        prepStatsUpdate.setDouble(10, cost / transactions);
      } else {
        prepStatsUpdate.setDouble(10, 0);
      }
      prepStatsUpdate.setDouble(11, firstPageCpc);      
      prepStatsUpdate.setLong(12, campaignId);
      
      prepStatsUpdate.addBatch();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }    
  }
  
  public void executeStatsBatch() {
    try {
      prepStatsUpdate.executeBatch();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  }
  
  /**
   * Get an AdGroup from DB based on its LocalId 
   */
  public CampaignStructure get(int localId) {
    PreparedStatement prep = null;
    CampaignStructure campaignStructure = null; 
    try {
      prep = conn.prepareStatement(
            "SELECT * FROM Campaigns WHERE localId = ?");
      prep.setInt(1, localId);
      ResultSet rs = prep.executeQuery();
      campaignStructure = getResultSet(rs);
      rs.close();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }     
    return campaignStructure;
  }  
}
